using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Xml;
using System.Data.SqlClient;
using System.IO;

public class BaseDB
{
	public static SqlConnection sqlConnect;
    //public static string GetConnectStrFromFile(string pFileSpec)
    //{
    //    string mPassFromFile = "";
    //    string mCnnStr = "";
    //    try {
    //        StreamReader sr = new StreamReader(pFileSpec);
    //        string line = null;
    //        do {
    //            line = sr.ReadLine();
    //            if ((line != null)) {
    //                string str = "Provider=SQLOLEDB.1;";
    //                dynamic index = line.IndexOf(str) + str.Length();
    //                if (index == str.Length()) {
    //                    mCnnStr = line.Substring(index);
    //                    break; // TODO: might not be correct. Was : Exit Do
    //                }
    //            }
    //        } while (!(line == null));
    //        sr.Close();
    //    } catch (Exception ex) {
    //        throw ex;
    //    }
    //    //mCnnStr &= ";Pooling = true; Min Pool Size = 1; Max Pool Size = 50"
    //    //myConnStr = mCnnStr
    //    return mCnnStr;
    //}

    //public static void GanCauLenhKetNoi(ref string strConnect, string sUserName, string sPassword)
    //{
    //    ThayTheGiaTri(ref strConnect, "User ID", sUserName);
    //    ThayTheGiaTri(ref strConnect, "Password", sPassword);
    //}
	//ham dung de thay gia tri cua mot thanh phan trong chuoi
    //public static void ThayTheGiaTri(ref string strConnect, string sThanhPhan, string sGiaTri)
    //{
    //    string sThayThe = sThanhPhan + "=" + sGiaTri;
    //    int viTriDau = strConnect.IndexOf(sThanhPhan);
    //    //neu tim thay
    //    if ((viTriDau != -1)) {
    //        //delete het cho den khi gap ";" hoac cuoi chuoi
    //        //tim vi tri cuoi cua chuoi nay

    //        int i = 0;
    //        while ((strConnect[viTriDau + i] != ";" & viTriDau + i < strConnect.Length)) {
    //            i += 1;
    //        }
    //        //delete
    //        strConnect = strConnect.Remove(viTriDau, i);
    //        //thay the bang chuoi moi
    //        strConnect = strConnect.Insert(viTriDau, sThayThe);
    //    } else {
    //        sThayThe = ";" + sThayThe;
    //        strConnect = string.Concat(strConnect, sThayThe);
    //    }
    //}

	public static void OpenConnect()
	{
		try {
			//Mo ket noi CSDL
            sqlConnect = new SqlConnection(@"Data Source=QUOCSY\SQLEXPRESS;Initial Catalog=ThietBi;User ID=sa;pwd=sql2005");
            //dynamic connectString = "";
            //if (string.IsNullOrEmpty(connectString)) {
            //    connectString = openConnStr;
            //    // GetConnectStrFromFile("DBConnect.udl")
            //    //connectString = GetConnectStrFromFile("DBConnect.udl")
            //}
            //sqlConnect.ConnectionString = connectString;

            ////Neu ket noi voi dbName khac
            //if (!string.IsNullOrEmpty(dbName)) {
            //    string sConnectString = sqlConnect.ConnectionString();
            //    int indexStart = 0;
            //    int indexEnd = 0;
            //    indexStart = sConnectString.IndexOf("Initial Catalog=") + 16;
            //    indexEnd = sConnectString.IndexOf(";Data Source");
            //    dynamic strReplace = sConnectString.Substring(indexStart, indexEnd - indexStart);
            //    sqlConnect.ConnectionString = sConnectString.Replace(strReplace, dbName);
            //}

			//GanCauLenhKetNoi(sqlConnect.ConnectionString, g_UserName, g_Password)
			if (sqlConnect.State == ConnectionState.Closed) {
				sqlConnect.Open();
			}
		} catch (Exception ex) {
			throw ex;
		}
	}

	public static void CloseConnect()
	{
		try {
			if (sqlConnect.State == ConnectionState.Open) {
				sqlConnect.Close();
			}
		} catch (Exception ex) {
			throw ex;
		}
	}

	//Khoi tao SqlCommand 1
	public static SqlCommand InitializeConnect_Command(string strProcedureName, params SqlParameter[] par)
	{
		//Thuc thi store procedure
		SqlCommand com = new SqlCommand(strProcedureName, sqlConnect);
		OpenConnect();
		com.Connection = sqlConnect;
		com.CommandType = CommandType.StoredProcedure;
        if (par.Length!=0)
        {
            for (int i = 0; i <= par.Length; i++)
            {
                com.Parameters.Add(par[i]);
            }
	
        }
            
		 
		return com;
	}

	//Khoi tao SqlCommand 2
	public static SqlCommand InitializeConnect_Command(string strProcedureName)
	{
		//Thuc thi store procedure
		SqlCommand com = new SqlCommand(strProcedureName);
		OpenConnect();
		com.Connection = sqlConnect;
		com.CommandType = CommandType.StoredProcedure;
		return com;
	}

	//Ham thuc thi nhung procedure kiem tra
	public static SqlCommand ExecSqlProcedure_Command(string strProcedureName, params SqlParameter[] par)
	{
		bool Result = false;
		SqlCommand com = null;
		com = InitializeConnect_Command(strProcedureName, par);
		long lRowsAffected = 0;
		try {
			com.ExecuteReader();
		} catch (Exception ex) {
			throw ex;
		}
		return com;
	}
	//Ham thuc thi pro nhung bo qua expride timeout, ko bat ex
	public static SqlCommand ExecSqlProcedure_Command(string strProcedureName)
	{
		bool Result = false;
		SqlCommand com = null;
		com = InitializeConnect_Command(strProcedureName);
		long lRowsAffected = 0;
		try {
			com.ExecuteReader();
		} catch (Exception ex) {
			//Throw ex
		}
		return com;
	}

	//Ham thuc thi nhung procedure insert, update, delete 1
	public static long ExecSqlProcedure_NoneQuery(string strProcedureName, params SqlParameter[] par)
	{
		bool Result = false;
		SqlCommand com = null;
		com = InitializeConnect_Command(strProcedureName, par);
		long lRowsAffected = 0;
		try {
			com.Transaction = com.Connection.BeginTransaction();
			lRowsAffected = com.ExecuteNonQuery();
			com.Transaction.Commit();
		} catch (Exception ex) {
			com.Transaction.Rollback();
			throw ex;
		} finally {
			com.Connection.Close();
		}
		return lRowsAffected;
	}

	public static long ExecSql_NoneQuery(string strSql)
	{
		SqlCommand com = null;
		com = new SqlCommand();
		long lRowsAffected = 0;
		try {
			com.CommandText = strSql;
			OpenConnect();
			com.Connection = sqlConnect;
			com.Transaction = com.Connection.BeginTransaction();
			lRowsAffected = com.ExecuteNonQuery();
			com.Transaction.Commit();
			com.Connection.Close();
		} catch (Exception ex) {
			com.Transaction.Rollback();
			throw ex;
		}
		return lRowsAffected;
	}

	//Thuc thi nhung query procedure 1
	public static DataSet ExecSqlProcedure_DataSet(string strProcedureName, params SqlParameter[] par)
	{
		bool Result = false;
		SqlCommand com = null;
		com = InitializeConnect_Command(strProcedureName, par);
		SqlDataAdapter myAdapter = new SqlDataAdapter(com);
		DataSet ds = new DataSet();
		myAdapter.Fill(ds, strProcedureName);
		com.Parameters.Clear();
		com.Connection.Close();
		return ds;
	}

	//Thuc thi nhung query procedure 2
	public static DataSet ExecSqlProcedure_DataSet(string strProcedureName)
	{
		bool Result = false;
		SqlCommand com = null;
		com = InitializeConnect_Command(strProcedureName);
		SqlDataAdapter myAdapter = new SqlDataAdapter(com);
		DataSet ds = new DataSet();
		try {
			myAdapter.Fill(ds, strProcedureName);
		} catch (Exception ex) {
			throw ex;
		} finally {
			com.Connection.Close();
		}
		return ds;
	}

    //
    

	public static SqlDataReader ExecSqlProcedure_DataReader(string strProcedureName, params SqlParameter[] par)
	{
		bool Result = false;
		SqlCommand com = null;
		com = InitializeConnect_Command(strProcedureName, par);
		SqlDataReader myReader = null;
		try {
			myReader = com.ExecuteReader();
		} catch (Exception ex) {
			throw ex;
		}
		return myReader;
	}

	public static SqlDataReader ExecSql_DataReader(string strSql)
	{
		SqlCommand com = new SqlCommand();
		com.CommandText = strSql;
		OpenConnect();
		com.Connection = sqlConnect;
		SqlDataReader myReader = null;
		try {
			myReader = com.ExecuteReader(CommandBehavior.CloseConnection);
		} catch (Exception ex) {
			throw ex;
		}
		return myReader;
	}


	public static DataSet ExecSql_DataSet(string strSql)
	{
		SqlCommand com = new SqlCommand();
		DataSet ds = new DataSet();
		try {
			com.CommandText = strSql;
			OpenConnect();
			com.Connection = sqlConnect;
			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand = com;
			da.Fill(ds);
		} catch (Exception ex) {
			throw ex;
		} finally {
			com.Connection.Close();
		}
		return ds;
	}
    public static DataSet ExecSql_DataSet(string strSql,string tableName)
    {
        SqlCommand com = new SqlCommand();
        DataSet ds = new DataSet();
        try
        {
            com.CommandText = strSql;
            OpenConnect();
            com.Connection = sqlConnect;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = com;
            da.Fill(ds,tableName);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            com.Connection.Close();
        }
        return ds;
    }
    public static SqlDataAdapter ExecSql_DataAdapter(string strSql )
    {
        SqlCommand com = new SqlCommand();
        DataSet ds = new DataSet();
        SqlDataAdapter da;
        try
        {
            com.CommandText = strSql;
            OpenConnect();
            com.Connection = sqlConnect;
            da = new SqlDataAdapter();
            da.SelectCommand = com;
            //da.Fill(ds, table );
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            com.Connection.Close();
        }
        return da;
    }

	public static object ExecSql_DataValue(string strSql)
	{
		SqlCommand com = new SqlCommand();
		com.CommandText = strSql;
		OpenConnect();
		com.Connection = sqlConnect;
		object result = null;
		try {
			result = com.ExecuteScalar();
		} catch (Exception ex) {
			throw ex;
		} finally {
			com.Connection.Close();
		}
		return result;
	}

	public static object ExecSqlProcedure_DataValue(string strProcedureName, params SqlParameter[] par)
	{
		SqlCommand com = null;
		com = InitializeConnect_Command(strProcedureName, par);
		object result = null;
		try {
			result = com.ExecuteScalar();
		} catch (Exception ex) {
			throw ex;
		} finally {
			com.Connection.Close();
		}
		return result;
	}

	public static object GetObjectValue(object value)
	{
		if (object.ReferenceEquals(value, DBNull.Value)) {
			return null;
		} else {
			return value;
		}
	}
}